﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class BankAccountManager:ConnectionManager
    {
        #region Adding/Updating
        public SqlException AddBankAccount(BankAccountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO OBNK_BRNCH (BankCode, BankName, BranchCode, BranchName, AccountNumber, AcctCode, AcctName) VALUES (@BankCode, @BankName, @BranchCode, @BranchName, @AccountNumber, @AcctCode, @AcctName)";
                oCommand.Parameters.Add(new SqlParameter("@BankCode", oUnit.BankCode));
                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@BranchCode", oUnit.BranchCode));
                oCommand.Parameters.Add(new SqlParameter("@BranchName", oUnit.BranchName));
                oCommand.Parameters.Add(new SqlParameter("@AccountNumber", oUnit.AccountNumber));
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBankAccount(BankAccountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OBNK_BRNCH SET BankCode=@BankCode, BankName=@BankName, BranchCode=@BranchCode, AccountNumber=@AccountNumber, BranchName=@BranchName, AcctCode=@GLAccountCode, AcctName=@AcctName WHERE BranchCode=@BranchCode";
                oCommand.Parameters.Add(new SqlParameter("@BankCode", oUnit.BankCode));
                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@BranchCode", oUnit.BranchCode));
                oCommand.Parameters.Add(new SqlParameter("@BranchName", oUnit.BranchName));
                oCommand.Parameters.Add(new SqlParameter("@AccountNumber", oUnit.AccountNumber));
                oCommand.Parameters.Add(new SqlParameter("@GLAccountCode", oUnit.AcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Retreiving datus
        public DataTable GetBankAccounts()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK_BRNCH";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseByBranchName(string sBranchName)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sBranchName = "%" + sBranchName.Trim() + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK_BRNCH WHERE BranchName LIKE @BranchName";

            oCommand.Parameters.Add(new SqlParameter("@BranchName", sBranchName));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseByBranchCode(string sBranchCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sBranchCode = "%" + sBranchCode.Trim() + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK_BRNCH WHERE BranchCode LIKE @BranchCode";

            oCommand.Parameters.Add(new SqlParameter("@BranchCode", sBranchCode));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseByAccountNumber(string sAccountNumber)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sAccountNumber = "%" + sAccountNumber.Trim() + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK_BRNCH WHERE AccountNumber LIKE @AccountNumber";

            oCommand.Parameters.Add(new SqlParameter("@AccountNumber", sAccountNumber));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetByAccountNumber(string sAccountNumber)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK WHERE AccountNumber = @AccountNumber";

            oCommand.Parameters.Add(new SqlParameter("@AccountNumber", sAccountNumber));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }
        #endregion
    }
}
